﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Table_Dal;
using System.Data.OleDb;
namespace DAL
{
    public class CauHoiDal:DataAccessLayer
    {
        DataDataContext dt;
        public DataTable LayDuLieuExcel(string DuongDan)
        {
            try
            {
                string excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + DuongDan.Trim() + ";" + "Extended Properties='Excel 8.0;HDR={1}'";
                OleDbConnection cn = new OleDbConnection(excelConnectionString);
                cn.Open();
                DataTable dtble = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                string l ="";
                int i = 0;
                foreach (DataRow item in dtble.Rows)
                {
                    if (i == 0)
                    {
                        l = item["TABLE_NAME"].ToString();
                        i++;
                    }
                }
                OleDbDataAdapter da = new OleDbDataAdapter("Select * from ["+l+"]", cn);
                cn.Close();
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds.Tables[0];
            }
            catch
            {
                return null;
            }
        }
        public void Import(string DuongDan)
           {
               int x = -1;
               if (DuongDan.IndexOf(".xls") > 0)
               {
                   x = 0;//Excel 2003 - 2007
               }
               if (DuongDan.IndexOf(".xlsx") > 0)
               {
                   x = 1; //Excel 2007
               }
            string excelstring="";
            if (x == 0)
            {
               excelstring = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + DuongDan.Trim() + ";" + "Extended Properties='Excel 8.0;HDR={1}'";
            }
            if (x == 1)
            {
                excelstring = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + DuongDan.Trim() + ";" + "Extended Properties='Excel 12.0;HDR={1}'";
            }

               OleDbConnection cn = new OleDbConnection();
               cn.ConnectionString = excelstring;
               cn.Open();
               string strSQL = "Select * From [Sheet1$]";
               OleDbCommand cmd = new OleDbCommand(strSQL, cn);
               OleDbDataReader dr;
               dr = cmd.ExecuteReader();
               DataAccessLayer db = new DataAccessLayer();
               string sql = chuoiketnoi;
               SqlBulkCopy bulk = new SqlBulkCopy(sql, SqlBulkCopyOptions.Default);
               bulk.DestinationTableName = "CAUHOI";
               bulk.WriteToServer(dr);
            }
        public bool XoaCauHoi(string Macauhoi)
        {
            try
            {
                dt = GetKetnoi();
                CAUHOI ch = dt.CAUHOIs.SingleOrDefault(k => k.MACAUHOI == Macauhoi);
                dt.CAUHOIs.DeleteOnSubmit(ch);
                dt.SubmitChanges();
            }
            catch
            {
                return false;
            }
            return true;
        }
        public List<CauHoiTbl> LayDanhSachCauHoi_MaChuong_DoKho(string MaChuong,int Dokho)
        {
            List<CauHoiTbl> DsCauHoi = new List<CauHoiTbl>();
            ChuongDal ch = new ChuongDal();
            dt = GetKetnoi();
            var q = dt.LayDanhSachCauHoi_MaChuong_DoKho(MaChuong, Dokho);
            foreach (var item in  q)
            {
                CauHoiTbl cauhoi = new CauHoiTbl();
                cauhoi.DoKho =int.Parse( item.DOKHO.ToString());
                cauhoi.HinhAnh =item.HINHANH.ToString();
                cauhoi.MaCauHoi = item.MACAUHOI.ToString();
                cauhoi.NoiDung = item.NOIDUNG.ToString();
                cauhoi.ThoiGian = int.Parse(item.THOIGIAN.ToString());
                DsCauHoi.Add(cauhoi);
            }
            return DsCauHoi;
        }
        public CauHoiTbl ThongTinCauHoi_TheoMaCauHoi_TenCauHoi(string MaCauHoi, string TenCauHoi)
        {
            ChuongDal ch = new ChuongDal();
            dt = GetKetnoi();
            var q = dt.ThongTinCauHoi_TheoMaCauHoi_TheoTenCauHoi(MaCauHoi, TenCauHoi);
            CauHoiTbl cauhoi = new CauHoiTbl();
            foreach (var item in q)
            {
                cauhoi = new CauHoiTbl();
                cauhoi.DoKho = int.Parse(item.DOKHO.ToString());
                cauhoi.HinhAnh = item.HINHANH.ToString();
                cauhoi.MaCauHoi = item.MACAUHOI.ToString();
                cauhoi.NoiDung = item.NOIDUNG.ToString();
                cauhoi.ThoiGian = int.Parse(item.THOIGIAN.ToString());
            }
            return cauhoi;
        }
        public bool ThemCauHoi(CauHoiTbl cauhoi)
        {
            try
            {
                dt = GetKetnoi();
                CAUHOI ch = new CAUHOI();
                ch.MACAUHOI = cauhoi.MaCauHoi;
                ch.NOIDUNG = cauhoi.NoiDung;
                ch.MACHUONG = cauhoi.MaChuong;
                ch.THOIGIAN = cauhoi.ThoiGian;
                ch.DOKHO = cauhoi.DoKho;
                string hinhanh = "";
                if (cauhoi.HinhAnh != null)
                    hinhanh = cauhoi.HinhAnh;
                ch.HINHANH = hinhanh;
                dt.CAUHOIs.InsertOnSubmit(ch);
                dt.SubmitChanges();
            }
            catch
            {
                return false;
            }
            return true;
        }
        public bool SuaCauHoi(CauHoiTbl cauhoi)
        {
            try
            {
                dt = GetKetnoi(); 
                CAUHOI da = dt.CAUHOIs.SingleOrDefault(i => i.MACAUHOI == cauhoi.MaCauHoi);
                da.NOIDUNG = cauhoi.NoiDung;
                da.HINHANH = cauhoi.HinhAnh;
                if (cauhoi.ThoiGian.ToString().Trim().Trim().Length > 0)
                {
                    da.THOIGIAN = cauhoi.ThoiGian;
                }
                da.DOKHO = cauhoi.DoKho;
                dt.SubmitChanges();
            }
            catch
            {
                return false;
            }
            return true;
        }
        public int SoLuongCauHoi_TheoMaChuong_TheoDoKho_TheoMon(string MaChuong, int Dokho, string MaMon)
        {
            dt = GetKetnoi();
            return dt.SoLuongCauHoi_TheoMaChuong_TheoDoKho_TheoMon(MaChuong, Dokho, MaMon);
        }
        public List<string> LayDanhSachCauHoi_MaChuong_DoKho_MaMon(string MaChuong, int Dokho, string MaMon)
        {
            dt = GetKetnoi();
            var q = dt.LayDanhSachCauHoi_MaChuong_DoKho_MaMon(MaChuong, Dokho, MaMon);
            List<string> dsCauHoi = new List<string>();
            foreach (var item in q)
            {
                string cauhoi = item.MACAUHOI;
                dsCauHoi.Add(cauhoi);
            }
            return dsCauHoi;
        }
        public List<CauHoiTbl> LayDanhSachCauHoi_MaChuong_DoKho_MaMon1(string MaChuong, int Dokho, string MaMon)
        {
            dt = GetKetnoi();
            var q = dt.LayDanhSachCauHoi_MaChuong_DoKho_MaMon(MaChuong, Dokho, MaMon);
            List<CauHoiTbl> dsCauHoi = new List<CauHoiTbl>();
            foreach (var item in q)
            {
                CauHoiTbl cauhoi = new CauHoiTbl();
                cauhoi.DoKho = int.Parse(item.DOKHO.ToString());
                cauhoi.HinhAnh = item.HINHANH;
                cauhoi.MaCauHoi = item.MACAUHOI;
                cauhoi.NoiDung = item.NOIDUNG;
                cauhoi.ThoiGian = int.Parse(item.THOIGIAN.ToString());
                dsCauHoi.Add(cauhoi);
            }
            return dsCauHoi;
        }
        public List<CauHoiTbl> Timkiemcauhoi(string chuoi, string MaMon, int Dokho, string Chuong)
        {

            List<CauHoiTbl> DsCauHoi = new List<CauHoiTbl>();
            dt = GetKetnoi();
            var q = dt.TimKiemCauHoi_TheoNoiDung_Macauhoi(chuoi, MaMon, Dokho, Chuong);
            foreach (var item in q)
            {
                CauHoiTbl cauhoi = new CauHoiTbl();
                cauhoi.DoKho = int.Parse(item.DOKHO.ToString());
                cauhoi.HinhAnh = item.HINHANH;
                cauhoi.MaCauHoi = item.MACAUHOI;
                cauhoi.NoiDung = item.NOIDUNG;
                cauhoi.ThoiGian = int.Parse(item.THOIGIAN.ToString());
                DsCauHoi.Add(cauhoi);
            }
            return DsCauHoi;
        }
        public string PictureLink(string Macauhoi)
        {
            dt = GetKetnoi();
            CAUHOI da = dt.CAUHOIs.SingleOrDefault(i => i.MACAUHOI == Macauhoi);
            if (da.HINHANH == "" || da.HINHANH == null)
                return "";
            else
                return da.HINHANH;
        }

        public List<CauHoiTbl> DSCauHoiDeThi(string Ma)
        {
            dt = GetKetnoi();
            IEnumerable<CAUHOI> q = from i in dt.CAUHOIs
                    join j in dt.CHITIETDETHIs on i.MACAUHOI equals j.MACAUHOI
                    where j.MADETHI == Ma
                    select i;
            List<CauHoiTbl> ds = new List<CauHoiTbl>();
            foreach (CAUHOI item in q)
            {
                CauHoiTbl ch = new CauHoiTbl();
                ch.MaCauHoi = item.MACAUHOI;
                ch.NoiDung = item.NOIDUNG;
                ch.HinhAnh = item.HINHANH;
                ds.Add(ch);
            }
            return ds;
        }
        public bool KiemTra_TrungKhoa(string Ma)
        {
            dt = GetKetnoi();
            IEnumerable<CAUHOI> q = dt.CAUHOIs.Where(k => k.MACAUHOI == Ma);
            foreach (CAUHOI item in q)
            {
                if (item.MACAUHOI == Ma)
                    return true;
            }
            return false;
        }

        public List<string> LayTatCa_string()
        {
            dt = GetKetnoi();
            IEnumerable<CAUHOI> ds = dt.CAUHOIs;
            List<string> dslst = new List<string>();
            foreach (CAUHOI item in ds)
            {
                dslst.Add(item.MACAUHOI);
            }

            return dslst;
        }
    }
}
